package com.myalibaba.druid;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class JdbcTest {

    @Test
    public void connectionDisabled() {
        String url = "jdbc:mysql://localhost:3306/emall";
        String username = "root";
        String password = "123456";
        // 创建Druid数据源
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setMaxActive(1);  //设置最大活动连接数
        ExecutorService executorService = Executors.newFixedThreadPool(3);
        for (int i = 0; i < 10; i++) {
            executorService.submit(() -> {
                try {
                    System.out.println(111);
                    // 获取数据库连接
                    DruidPooledConnection connection = dataSource.getConnection();
                    // 执行操作
                    PreparedStatement statement = connection.prepareStatement("SELECT 1");
                    // 关闭连接
                    statement.close();
                    connection.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            });
        }
        System.out.println(222);
        executorService.shutdown();
        dataSource.close(); // 关闭数据源
    }

    /**
     * 模拟错误
     * Caused by: java.sql.SQLException: connection disabled
     */
    @Test
    public void connectionClosed() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/emall");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        try (Connection connection = dataSource.getConnection()) {
            // 通过关闭连接来模拟 connection disabled
            connection.close();
            // 尝试在关闭的连接上准备语句，触发 SQLException
            PreparedStatement stmt = connection.prepareStatement("SELECT 1");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dataSource.close();
        }
    }



    @Test
    public  void insertTestData() {
        String jdbcUrl = "jdbc:mysql://localhost:3306/emall?connectTimeout=20000&socketTimeout=20000&queryTimeout=20000";
        String username = "root";
        String password = "123456";

        int totalRecords = 200000;
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            String sql = "INSERT INTO `emall`.`member2` (`id`, `name`, `password`, `grade`, `grade_value`, `integral`, `register_time`, `last_login_time`, `last_login_ip`, `login_number`, `last_address_id`, `last_payment_code`, `gender`, `birthday`, `email`, `qq`, `mobile`, `phone`, `pwd_err_count`, `source`, `balance`, `balance_pwd`, `is_email_verify`, `is_sms_verify`, `sms_verify_code`, `email_verify_code`, `can_receive_sms`, `can_receive_email`, `status`, `update_time`, `session_id`, `token`, `sms_code`, `org_first`, `org_second`, `org_third`, `org_fourth`, `head_img`, `member_type`, `channel`) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NULL, 0, NULL, NULL, NULL, ?, ?, 1, ?, ?, ?, ?, ?, ?, 1, 1, 1, ?, ?, NULL, ?, NULL, 8171400, 8171413, 8171414, 8171415, NULL, 0, NULL)";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                for (int i = 1; i <= totalRecords; i++) {
                    System.out.println(i);
                    statement.setInt(1, i);
                    statement.setString(2, "user" + i);
                    statement.setString(3, UUID.randomUUID().toString().replace("-", ""));
                    statement.setInt(4, 3);
                    statement.setInt(5, (int) (Math.random() * 1000));
                    statement.setInt(6, (int) (Math.random() * 1000));
                    statement.setString(7, dtf.format(LocalDateTime.now().minusDays((long) (Math.random() * 365))));
                    statement.setString(8, dtf.format(LocalDateTime.now().minusDays((long) (Math.random() * 30))));
                    statement.setString(9, "127.0.0." + (i % 255));
                    statement.setInt(10, (int) (Math.random() * 100));
                    statement.setString(11, String.format("%011d", i));
                    statement.setString(12, String.format("%011d", i));
                    statement.setInt(13, 1);
                    statement.setDouble(14, Math.random() * 10000);
                    statement.setString(15, UUID.randomUUID().toString().replace("-", ""));
                    statement.setInt(16, (int) (Math.random() * 2));
                    statement.setInt(17, 1);
                    statement.setInt(18, 1);
                    statement.setInt(19, 1);
                    statement.setString(20, dtf.format(LocalDateTime.now()));
                    statement.setString(21, UUID.randomUUID().toString().replace("-", ""));
                    statement.addBatch();
                    if (i % 1000 == 0 || i == totalRecords) {
                        statement.executeBatch();
                    }
                }
            }
            System.out.println("Data insertion completed successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }





    @Test
    public  void queryTimeoutTest() {
        String jdbcUrl = "jdbc:mysql://localhost:3306/emall?connectTimeout=20000&socketTimeout=20000&queryTimeout=20000";
        String username = "root";
        String password = "123456";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             Statement stmt = conn.createStatement()) {

            // 设置查询超时时间为60秒
            stmt.setQueryTimeout(60);

            String sql = "SELECT * FROM member WHERE id = 15";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                // 处理结果集
                System.out.println("Column Value: " + rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
